CREATE TABLE [dbo].TblCountry(
Id INT not NULL PRIMARY KEY identity,
Name VARCHAR(50) not NULL,
Code VARCHAR(3) not NULL,
UpdatedOn DateTime not null DEFAULT getdate(),
CreatedOn DateTime not null DEFAULT getdate(),
StatusId int not null default 0
);

CREATE TABLE [dbo].TblCity(
Id INT not NULL PRIMARY KEY identity,
Name VARCHAR(50) not NULL,
Code VARCHAR(3) not NULL,
CountryId int not null,
UpdatedOn DateTime not null DEFAULT getdate(),
CreatedOn DateTime not null DEFAULT getdate(),
StatusId int not null default 0
);
ALTER TABLE [dbo].TblCity ADD CONSTRAINT City_Country_FK FOREIGN KEY (CountryId) REFERENCES TblCountry(Id);

CREATE TABLE [dbo].TblArea(
Id INT not NULL PRIMARY KEY identity,
Name VARCHAR(50) not NULL,
Code VARCHAR(30) not NULL,
CityId int not null,
Description VARCHAR(100),
UpdatedOn DateTime not null DEFAULT getdate(),
CreatedOn DateTime not null DEFAULT getdate(),
StatusId int not null default 0
);
ALTER TABLE [dbo].TblArea ADD CONSTRAINT City_Area_FK FOREIGN KEY (CityId) REFERENCES TblCity(Id);

CREATE TABLE [dbo].TblZone(
Id INT not NULL PRIMARY KEY identity,
Name VARCHAR(50) not NULL,
Code VARCHAR(30) not NULL,
Description VARCHAR(100),
AreaId int not null,
UpdatedOn DateTime not null DEFAULT getdate(),
CreatedOn DateTime not null DEFAULT getdate(),
StatusId int not null default 0
);
ALTER TABLE [dbo].TblZone ADD CONSTRAINT Zone_Area_FK FOREIGN KEY (AreaId) REFERENCES TblArea(Id);

CREATE TABLE [dbo].TblClient(
Id INT not NULL PRIMARY KEY identity,
Name VARCHAR(50) not NULL,
Code VARCHAR(30) not NULL,
Description VARCHAR(100),
UpdatedOn DateTime not null DEFAULT getdate(),
CreatedOn DateTime not null DEFAULT getdate(),
StatusId int not null default 0
);
CREATE TABLE [dbo].TblUser(
Id INT not NULL PRIMARY KEY identity,
Username VARCHAR(50) not NULL,
FirstName VARCHAR(30) not NULL,
LastName VARCHAR(30) not NULL,
Password VARCHAR(200) not NULL,
Email VARCHAR(100) not NULL,
MobileNo varchar (20) NOT NULL,
LastLogin DateTime ,
UserTypeId Int not null,
UpdatedOn DateTime not null DEFAULT getdate(),
CreatedOn DateTime not null DEFAULT getdate(),
StatusId int not null default 0
);
CREATE TABLE [dbo].TblReason(
Id INT not NULL PRIMARY KEY identity,
Name VARCHAR(50) not NULL,
Code VARCHAR(30) not NULL,
ReasonTypeId int not null,
Description VARCHAR(100),
UpdatedOn DateTime not null DEFAULT getdate(),
CreatedOn DateTime not null DEFAULT getdate(),
StatusId int not null default 0
);
CREATE TABLE [dbo].TblProduct(
Id INT not NULL PRIMARY KEY identity,
Name VARCHAR(50) not NULL,
Code VARCHAR(30) not NULL,
Description VARCHAR(100),
ClientId int not null,
Price decimal (18, 4) NOT NULL DEFAULT 0,
UpdatedOn DateTime not null DEFAULT getdate(),
CreatedOn DateTime not null DEFAULT getdate(),
StatusId int not null default 0
);
ALTER TABLE [dbo].TblProduct ADD CONSTRAINT Product_Client_FK FOREIGN KEY (ClientId) REFERENCES TblClient(Id);

CREATE TABLE [dbo].TblPointOfPurchaseType(
Id INT not NULL PRIMARY KEY identity,
Name VARCHAR(50) not NULL,
Code VARCHAR(30) not NULL,
Description VARCHAR(100),
UpdatedOn DateTime not null DEFAULT getdate(),
CreatedOn DateTime not null DEFAULT getdate(),
StatusId int not null default 0
);
CREATE TABLE [dbo].TblPointOfPurchase(
Id INT not NULL PRIMARY KEY identity,
Name VARCHAR(50) not NULL,
Code VARCHAR(30) not NULL,
Description VARCHAR(100),
PointOfPurchaseTypeId int not null,
ZoneId int not null,
UpdatedOn DateTime not null DEFAULT getdate(),
CreatedOn DateTime not null DEFAULT getdate(),
StatusId int not null default 0
);
ALTER TABLE [dbo].TblPointOfPurchase ADD CONSTRAINT PointOfPurchase_PointOfPurchaseType_FK FOREIGN KEY (PointOfPurchaseTypeId ) REFERENCES TblPointOfPurchaseType(Id);
ALTER TABLE [dbo].TblPointOfPurchase ADD CONSTRAINT PointOfPurchase_Zone_FK FOREIGN KEY (ZoneId ) REFERENCES TblZone(Id);

CREATE TABLE [dbo].TblFrsAssign (
Id INT not NULL PRIMARY KEY identity,
ClientId INT not NULL,
AreaId INT not NULL,
UserId int Not NULL,
MondayZone int  NULL,
TuesdayZone int  NULL,
WednesdayZone int  NULL,
ThursdayZone int NULL,
FridayZone int NULL,
SaturdayZone int NULL,
SundayZone int NULL,
UpdatedOn DateTime not null DEFAULT getdate(),
CreatedOn DateTime not null DEFAULT getdate(),
StatusId int not null default 0
);
ALTER TABLE [dbo].TblFrsAssign ADD CONSTRAINT FrsAssign_Client_FK FOREIGN KEY (ClientId ) REFERENCES TblClient(Id);
ALTER TABLE [dbo].TblFrsAssign ADD CONSTRAINT FrsAssign_Area_FK FOREIGN KEY (AreaId ) REFERENCES TblArea(Id);
ALTER TABLE [dbo].TblFrsAssign ADD CONSTRAINT FrsAssign_User_FK FOREIGN KEY (UserId ) REFERENCES TblUser(Id);


CREATE TABLE [dbo].TblPOS(
Id INT not NULL PRIMARY KEY identity,
Name VARCHAR(50) not NULL,
Code VARCHAR(30) not NULL,
Description VARCHAR(100),
ClientId int not null,
UpdatedOn DateTime not null DEFAULT getdate(),
CreatedOn DateTime not null DEFAULT getdate(),
StatusId int not null default 0
);
ALTER TABLE [dbo].TblPOS ADD CONSTRAINT POS_Client_FK FOREIGN KEY (ClientId) REFERENCES TblClient(Id);

CREATE TABLE [dbo].TblPurchase(
Id [uniqueidentifier] not NULL PRIMARY KEY ,
DocumentRef VARCHAR(100) not null,
ClientId int not null,
PopId int not null,
FsrId int not null,
UpdatedOn DateTime not null DEFAULT getdate(),
CreatedOn DateTime not null DEFAULT getdate(),
StatusId int not null default 0
);
ALTER TABLE [dbo].TblPurchase ADD CONSTRAINT Purchase_Client_FK FOREIGN KEY (ClientId) REFERENCES TblClient(Id);
ALTER TABLE [dbo].TblPurchase ADD CONSTRAINT Purchase_FSR_FK FOREIGN KEY (FsrId) REFERENCES TblUser(Id);
ALTER TABLE [dbo].TblPurchase ADD CONSTRAINT Purchase_POP_FK FOREIGN KEY (PopId) REFERENCES TblPointOfPurchase(Id);

CREATE TABLE [dbo].TblPurchaseItem(
Id [uniqueidentifier] not NULL PRIMARY KEY ,
ProductId int not null,
ReasonId int not null,
PurchaseId [uniqueidentifier] not null,
Quantity Decimal(18,2) default 0,
Price DECIMAL(18,2) not null default 0,
UpdatedOn DateTime not null DEFAULT getdate(),
CreatedOn DateTime not null DEFAULT getdate(),
StatusId int not null default 0
);
ALTER TABLE [dbo].TblPurchaseItem ADD CONSTRAINT PurchaseItem_Product_FK FOREIGN KEY (ProductId) REFERENCES TblProduct(Id);
ALTER TABLE [dbo].TblPurchaseItem ADD CONSTRAINT PurchaseItem_Purchase_FK FOREIGN KEY (PurchaseId) REFERENCES TblPurchase(Id);

ALTER TABLE [dbo].TblUser Add [Enabled] bit  not null default 0;
ALTER TABLE [dbo].TblUser Add [ChangePass] bit  not null default 0;

CREATE TABLE [dbo].TblUserGroup(
Id INT not NULL PRIMARY KEY identity,
Name varchar(50) not null,
UpdatedOn DateTime not null DEFAULT getdate(),
CreatedOn DateTime not null DEFAULT getdate(),
StatusId int not null default 0
);

CREATE TABLE [dbo].TblRole(
Id INT not NULL PRIMARY KEY identity,
Name varchar(20) not null,
Description varchar(50) not null,
UpdatedOn DateTime not null DEFAULT getdate(),
CreatedOn DateTime not null DEFAULT getdate(),
StatusId int not null default 0
);

CREATE TABLE [dbo].TblUserGroupRole(
Id INT not NULL PRIMARY KEY identity,
UserGroupId int not null,
RoleId int not null,
UpdatedOn DateTime not null DEFAULT getdate(),
CreatedOn DateTime not null DEFAULT getdate(),
StatusId int not null default 0
);
ALTER TABLE [dbo].TblUserGroupRole ADD CONSTRAINT UserGroupRole_Role_FK FOREIGN KEY (RoleId) REFERENCES TblRole(Id);
ALTER TABLE [dbo].TblUserGroupRole ADD CONSTRAINT UserGroupRole_UserGroup_FK FOREIGN KEY (UserGroupId) REFERENCES TblUserGroup(Id);

ALTER TABLE [dbo].TblUser Add [UserGroupId] int  not null default 1;

ALTER TABLE [dbo].TblUser ADD CONSTRAINT User_UserGroup_FK FOREIGN KEY (UserGroupId) REFERENCES TblUserGroup(Id);
ALTER TABLE [dbo].TblUserGroupRole Add [CanAcces] bit  not null default 0;






CREATE TABLE [dbo].TblAuditTrail(
Id INT not NULL PRIMARY KEY identity,
Action VARCHAR(50) not NULL,
UserId int not null,
Description VARCHAR (100) NULL,
UpdatedOn DateTime not null DEFAULT getdate(),
CreatedOn DateTime not null DEFAULT getdate(),
StatusId int not null default 0
);
ALTER TABLE [dbo].TblAuditTrail ADD CONSTRAINT Audit_User_FK FOREIGN KEY (UserId) REFERENCES TblUser(Id);
----14/3/2012-------------------------------------------------------------------------------------
ALTER TABLE [dbo].TblProduct Add [Milles] decimal(16,4)  not null default 1;

ALTER TABLE [dbo].TblAuditTrail drop column [Action] ;
ALTER TABLE [dbo].TblAuditTrail Add [ActionTypeId] decimal  not null default 0;

ALTER TABLE [dbo].TblProduct  ALTER  column [Milles] decimal(16,4) ;

CREATE TABLE [dbo].TblPointOfPurchaseVist(
Id INT not NULL PRIMARY KEY identity,
PurchaseId [uniqueidentifier] not NULL,
IsSale bit not NULL DEFAULT 0,
VistDate DateTime not null DEFAULT getdate(),
UpdatedOn DateTime not null DEFAULT getdate(),
CreatedOn DateTime not null DEFAULT getdate(),
StatusId int not null default 0
);
ALTER TABLE [dbo].TblPointOfPurchaseVist ADD CONSTRAINT TblPointOfPurchaseVist_TblPurchase_FK FOREIGN KEY (PurchaseId) REFERENCES TblPurchase(Id);

------23-04-2012-------------------------------------------------------

ALTER TABLE [dbo].TblFrsAssign Add [Target] int  not null default 0;
----------------------24-04-2012---------------------------------------
CREATE TABLE [dbo].TblNews(
Id INT not NULL PRIMARY KEY identity,
Narration varchar(500) NOT NULL,
Subject varchar(150) NOT NULL,
UpdatedOn DateTime not null DEFAULT getdate(),
CreatedOn DateTime not null DEFAULT getdate(),
StatusId int not null default 0
);

---------------------------13-7-2012------------------
--ALTER TABLE [dbo].TblPointOfPurchaseVist  ALTER  column [PurchaseId] [uniqueidentifier] ;
--ALTER TABLE [dbo].TblPurchaseItem  ALTER  column [PurchaseId] [uniqueidentifier] ;
--ALTER TABLE [dbo].TblPurchaseItem  ALTER  column [Id] [uniqueidentifier] ;
--ALTER TABLE [dbo].TblPurchase  ALTER  column [Id] [uniqueidentifier] ;

CREATE TABLE [dbo].[TblApplication](
	[Id] [int] IDENTITY(1,1) NOT NULL Primary Key,
	[UserId] [int] NOT NULL,
	[UpdatedOn] [datetime] NOT NULL DEFAULT getdate(),
	[CreatedOn] [datetime] NOT NULL DEFAULT getdate(),
	[StatusId] [int] NOT NULL default 0,
	)
ALTER TABLE [dbo].[TblApplication]  ADD  CONSTRAINT [TblApplication_TblUser_FK] FOREIGN KEY([UserId]) REFERENCES [dbo].[TblUser] ([Id])

ALTER TABLE [dbo].TblClient Add [SalesmanTarget] int  not null default 8500;

CREATE TABLE [dbo].[TblProductBrand](
	[Id] [int] IDENTITY(1,1) NOT NULL Primary Key,
	[Name] varchar(50)  NOT NULL,
	[UpdatedOn] [datetime] NOT NULL DEFAULT getdate(),
	[CreatedOn] [datetime] NOT NULL DEFAULT getdate(),
	[StatusId] [int] NOT NULL default 1,
	)
SET IDENTITY_INSERT [dbo].[TblProductBrand] ON
INSERT [dbo].[TblProductBrand] ([Id], [Name], [UpdatedOn], [CreatedOn], [StatusId]) VALUES (1, N'BRAND', CAST(0x0000A0D6013FD06C AS DateTime), CAST(0x0000A0D6013FD06C AS DateTime), 1)
SET IDENTITY_INSERT [dbo].[TblProductBrand] OFF
	
ALTER TABLE [dbo].TblProduct Add [BrandId] int  not null default 1;
ALTER TABLE [dbo].[TblProduct]  ADD  CONSTRAINT [TblProduct_TblBrand_FK] FOREIGN KEY([BrandId]) REFERENCES [dbo].[TblProductBrand] ([Id])
